package CCRMServer.Controllers;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import CCRMClient.ClientStart;
import CCRMServer.DBConnect;
import Common.SERVER_COMMANDS;
import Common.ServerAnswer;
import Common.Entities.CustomersInTemplate;
import Common.Entities.MarketingTemplate;

/**
 * This class manage the actions that can be done on marketing templates by server side
 * @author AL
 *
 */
public class TemplateServerManager 
{

	/**
	 * This method register a new Template into the DB
	 * @param tmp - template to add
	 * @return Server answer with a relevant message or null in case of insertion problem 
	 */
	public static ServerAnswer AddTemplate(MarketingTemplate tmp) 
	{
		String filter = "TemplateID = '" + tmp.getTemplateID() + "'";
		String filter1 = "productID = '" + tmp.getProductID() + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, "marketingtemplates", null, filter);
			ResultSet rs1 = DBConnect.selectWithFilter(conDB, "products", null, filter1);
			ResultSet rs2 = DBConnect.selectWithFilter(conDB, "marketingtemplates", null, filter1);
			if (rs.next()) 
				return new ServerAnswer(SERVER_COMMANDS.ADD_TEMPLATE, null, "Template already exists");
			if (!rs1.next()) 
				return new ServerAnswer(SERVER_COMMANDS.ADD_TEMPLATE, null, "Product not exists in DB");
			else if (rs1.getInt(5) != 1) 
				return new ServerAnswer(SERVER_COMMANDS.ADD_TEMPLATE, null, "Product status is not active");
			if (rs2.next()) 
				return new ServerAnswer(SERVER_COMMANDS.ADD_TEMPLATE, null, "Product already related to another template");
			String[] fields = {"templateID", "templateName", "templateDescription", "templateMessage", "productID", "templateStatus"};
			String[] values = {Integer.toString(tmp.getTemplateID()), tmp.getTemplateName(), tmp.getTemplateDescription(), 
					tmp.getTemplateMessage(), Integer.toString(tmp.getProductID()), "in building process"};
			boolean isOK = DBConnect.insertSingleRecord(conDB, "marketingtemplates", fields, values);
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.ADD_TEMPLATE, null, "Action succeeded");
			return null;
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADD_TEMPLATE, null, "DB Problem");
		}
	}	
	
	/**
	 * this method search template in DB by ID
	 * @param Temp - marketing template entity 
	 * @return server answer with the SEARCH_TEMPLATE command, and message to show
	 */
	public static ServerAnswer searchTemplateByID (MarketingTemplate Temp) 
	{
		{
			String filter = "TemplateID = '" + Temp.getTemplateID() + "'";
			Connection conDB = DBConnect.mySQLConnection();
			try {
				ResultSet rs = DBConnect.selectWithFilter(conDB, "marketingtemplates", null, filter);
				if (!rs.next()) 
					return new ServerAnswer(SERVER_COMMANDS.SEARCH_TEMPLATE, null, "Template not exists");
				Temp.setTemplateID(Integer.parseInt(rs.getString(1)));
				Temp.setTemplateName(rs.getString(2));
				Temp.setTemplateDescription(rs.getString(3));
				Temp.setTemplateMessage(rs.getString(4));
				Temp.setProductID(Integer.parseInt(rs.getString(5)));
				Temp.setTemplateStatus(rs.getString(6));
				return new ServerAnswer(SERVER_COMMANDS.SEARCH_TEMPLATE, Temp, "Action succeded");
			} catch (SQLException e) 
			{
				return new ServerAnswer(SERVER_COMMANDS.SEARCH_TEMPLATE, null, "DB Problem");
			}
		}
	}
	
	/**
	 * this method view template from DB
	 * @param Temp - marketing template entity 
	 * @return server answer with the VIEW_TEMPLATE command, and message to show. if succeeded also the template details
	 */
	public static ServerAnswer ViewTemplateByID (MarketingTemplate Temp) 
	{
		{
			String filter = "TemplateID = '" + Temp.getTemplateID() + "'";
			Connection conDB = DBConnect.mySQLConnection();
			try {
				ResultSet rs = DBConnect.selectWithFilter(conDB, "marketingtemplates", null, filter);
				if (!rs.next()) 
					return new ServerAnswer(SERVER_COMMANDS.VIEW_TEMPLATE, null, "Template not exists");
				Temp.setTemplateID(Integer.parseInt(rs.getString(1)));
				Temp.setTemplateName(rs.getString(2));
				Temp.setTemplateDescription(rs.getString(3));
				Temp.setTemplateMessage(rs.getString(4));
				Temp.setProductID(Integer.parseInt(rs.getString(5)));
				Temp.setTemplateStatus(rs.getString(6));
				return new ServerAnswer(SERVER_COMMANDS.VIEW_TEMPLATE, Temp, "Action succeded");
			} catch (SQLException e) 
			{
				return new ServerAnswer(SERVER_COMMANDS.VIEW_TEMPLATE, null, "DB Problem");
			}	
		}
	}
	
	/**
	 * this method update template in DB
	 * @param tmp
	 * @return
	 */
	public static ServerAnswer UpdateTemplate(MarketingTemplate tmp) 
	{
		String filter1 = "productID = '" + tmp.getProductID() + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs1 = DBConnect.selectWithFilter(conDB, "products", null, filter1);
			if (!rs1.next()) 
				return new ServerAnswer(SERVER_COMMANDS.UPDATE_TEMPLATE, null, "Product not exists in DB");
			String[] fields = {"templateID", "templateName", "templateDescription", "templateMessage", "productID", "templateStatus"};
			String[] values = {Integer.toString(tmp.getTemplateID()), tmp.getTemplateName(), tmp.getTemplateDescription(), 
					tmp.getTemplateMessage(), Integer.toString(tmp.getProductID()), tmp.getTemplateStatus()};
			String[] keyName = {"templateID"};
			String[] keyVal = {Integer.toString(tmp.getTemplateID())};
			boolean isOK = DBConnect.updateSingleRecord(conDB, "marketingtemplates", fields, values ,keyName, keyVal );
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.UPDATE_TEMPLATE, null, "Action succeeded");
			return null;
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.UPDATE_TEMPLATE, null, "DB Problem");
		}
	}	
	
	/**
	 * this method search template in DB by status (search string)
	 * @param searchString
	 * @return Result Set with the relevant templates.
	 */
	public static ResultSet SearchTemplateByStatus(String searchString) {
		String fields = "templateID, templateName, templateDescription, templateMessage, productID, templateStatus";
		String filter = "templateStatus = '" + searchString + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, "MarketingTemplates", fields, filter);
			return rs;
		} catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * This method retrieve customers rates, permissions and personal record from the DB.
	 * If customer is rated above a given rate and
	 * gave permission for the template's product and
	 * active
	 * then customer's data is being added to the returned matrix.
	 * @param data - an array of 2 integers with the format: {minimumRate, templateID}
	 * @return - search results matrix (could be an empty matrix) or null in case of DB problem
	 */
	public static ArrayList<String[]> SearchCustRates(Integer[] data) {
		ArrayList<String[]> matrix = new ArrayList<String[]>();
		ArrayList<String> prodCats = new ArrayList<String>();
		ArrayList<String[]> custRates = new ArrayList<String[]>();
		String[] cRate = new String[3];
		String[] row = new String[5];
				
		try {
			// get product categories
			String table1 = "productsintypes PIT, catalogtypes CT, marketingtemplates M";
			String fields1 = "DISTINCT CT.ctegoryName";
			String filter1 = "M.templateID = '" + data[1].toString() +
								"' AND M.productID = PIT.productID AND PIT.typeID = CT.typeID";
			Connection conDB = DBConnect.mySQLConnection();
			ResultSet rs = DBConnect.selectWithFilter(conDB, table1, fields1, filter1);
			while (rs.next()) { prodCats.add(rs.getString(1)); }
			if (prodCats.isEmpty()) return matrix;
			rs.close();
			conDB.close();
			
			// get rates above minimal rate and customer IDs from Marketing Segments DB
			table1 = "customersrates";
			fields1 = null;
			filter1 = "rate >= '" + data[0].toString() + "'";
			conDB = DBConnect.MarketingSegmentsConnect();
			rs = DBConnect.selectWithFilter(conDB, table1, null, filter1);
			while (rs.next()) {
				cRate[0] = rs.getString(1);
				cRate[1] = rs.getString(2);
				custRates.add(cRate.clone());
			}
			rs.close();
			conDB.close();
			
			// for each rated customer, check permission
			conDB = DBConnect.mySQLConnection();
			table1 = "categoriespermissions";
			fields1 = "permissionType";
			for (int i = 0 ; i < custRates.size() ; i ++) {
				cRate = custRates.get(i);
				for (int j = 0 ; j < prodCats.size() ; j ++) {
					filter1 = "customerID = '" + cRate[0] + "' AND " +
							"permissionID = " + 
							"(SELECT MAX(permissionID) FROM categoriespermissions WHERE customerID = '" + cRate[0] +
							"' AND categoryName = '" + prodCats.get(j) + "')";
					rs = DBConnect.selectWithFilter(conDB, table1, fields1, filter1);
					if (rs.next()) {
						cRate[2] = rs.getString(1);
						if (rs.getInt(1) == 1) j = prodCats.size();
					} else
						cRate[2] = "2";
				}
			}
			rs.close();
			
			// for each rated customer, get data from CCRM DB
			table1 = "customers C, regions R";
			fields1 = "C.customerStatus, C.customerName, C.customerBrithday, R.regionName";
						
			long currentTime = System.currentTimeMillis();
			Calendar now = Calendar.getInstance();
			now.setTimeInMillis(currentTime);
			int currMonth = now.get(Calendar.MONTH) + 1;
			
			for (int i = 0 ; i < custRates.size() ; i ++) {
				cRate = custRates.get(i);
				
				// if permission is OK - query data
				if (Integer.parseInt(cRate[2]) == 1) {
					filter1 = "C.regionID = R.regionID AND C.customerID = '" + cRate[0] + "'";
					rs = DBConnect.selectWithFilter(conDB, table1, fields1, filter1);
					
					// if customer exists and active - add to matrix
					if (rs.next() && rs.getInt(1) == 1) {
						row[0] = cRate[0].toString();
						row[1] = rs.getString(2);
						
						// calculate customer's age
						Date bDate = rs.getDate(3);
						Calendar birthDay = Calendar.getInstance();
						birthDay.setTimeInMillis(bDate.getTime());
						int age = now.get(Calendar.YEAR) - birthDay.get(Calendar.YEAR);
						int birthMonth = birthDay.get(Calendar.MONTH) + 1;
						if (currMonth < birthMonth) age --;
						row[2] = Integer.toString(age);
						row[3] = rs.getString(4);
						row[4] = cRate[1];
						matrix.add(row.clone());
					}
					rs.close();
				}
			}
			return matrix;
		} catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * This method register a list of customers to a specific template in the CCRM DB
	 * @param toAssign - list of customer to associate with the template
	 * @return - server answer object with an action status message.
	 */
	public static ServerAnswer AssignCustomerToTemplate(ArrayList<CustomersInTemplate> toAssign) {
		Connection conDB = DBConnect.mySQLConnection();
		String table = "customersintemplates";
		String[] fields = {"customerID", "templateID"};
		String[] values = new String[2];
		try {
			for (CustomersInTemplate cit : toAssign) {
				// check if customer is already associated with the template
				String filter = "customerID = '" + cit.getCustomerID() + "' AND templateID = '" + Integer.toString(cit.getTemplateID()) + "'";
				ResultSet rs = DBConnect.selectWithFilter(conDB, table, null, filter);
				// associate customer to template
				if (!rs.next()) {
					values[0] = cit.getCustomerID();
					values[1] = Integer.toString(cit.getTemplateID());
					DBConnect.insertSingleRecord(conDB, table, fields, values);
				}
				rs.close();
			}
			return new ServerAnswer(SERVER_COMMANDS.ASSIGN_CUSTOMERS_TO_TEMPLATE, null, "Action succeeded");
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ASSIGN_CUSTOMERS_TO_TEMPLATE, null, "DB Problem");
		}
	}	
}
